package com.hnkywl.sts.dao.htgl;

import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.entity.htgl.Xsht;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.criterion.*;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.hibernate.transform.Transformers;
import org.hibernate.type.Type;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.*;

/**
 * Created by HL on 2016/4/18.
 */
@Repository
public class ZjbchtDao extends SimpleHibernateTemplate<Xsht, Long> {


    public Page<Map> findbzjyjList1(String htbm,int pageNo,int pageSize) {
        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();

        List<String> condition = new ArrayList<String>();
        String sql = "      select   \n" +
                "                                  xsd.xsdh as \"xsdh\",\n" +
                "                                      xsht.id as \"id\",\n" +
                "                                      xsd.id as \"xsdId\",\n" +
                "                                       xsht.htbm as \"htbm\",\n" +
                "                                       cght.htbm as \"cghtbm\",\n" +
                "                                      hzf.qc as \"hzf_qc\",\n" +
                "                                        xsht.xslx as \"xslx\",                 \n" +
                "                                       to_char(xsht.date_created,'yyyy/mm/dd') as \"htrq\",\n" +
                "                                          (select nvl(sum(t.sbsl),0) from t_biz_htmx t where t.ht_id=xsht.id  and t.deleted!=1) as \"htzl\",\n" +
                "                                           (select NVL(sum(cgmx.sbsl),0) from t_biz_cgd cgd left join t_biz_cgdmx cgmx on cgmx.cgd_id=cgd.id where cgd.cght_id=cght.id and cgd.deleted !=1) as \"cgzl\",\n" +
                "                                   \n" +
                "                                           ( select NVL(sum(xsmx.sbsl),0) from t_biz_xsd x left join t_biz_xsmx xsmx on xsmx.xsd_id=x.id where x.id=xsd.id and x.deleted!=1) as \"xszl\",\n" +
                "                                        \n" +
                "                                          (select NVL(sum(xsmx.sbsl*xsmx.hsj),0) from t_biz_xsd x left join t_biz_xsmx xsmx on xsmx.xsd_id=x.id where x.id=xsd.id  and x.deleted!=1) as \"xsje\",          \n" +
                "                              \n" +
                "                                            (select NVL(sum(xsmx.sbsl*xsmx.sjdj),0) from t_biz_xsd x left join t_biz_xsmx xsmx on xsmx.xsd_id=x.id where x.id=xsd.id  and x.deleted!=1) as \"sjje\",     \n" +
                "                   (select NVL(sum(xsmx.sbsl*xsmx.hsj),0) from t_biz_xsd x left join t_biz_xsmx xsmx on xsmx.xsd_id=x.id where x.id=xsd.id  and x.deleted!=1)-(select NVL(sum(xsmx.sbsl*xsmx.sjdj),0) from t_biz_xsd x left join t_biz_xsmx xsmx on xsmx.xsd_id=x.id where x.id=xsd.id  and x.deleted!=1) as \"ce\", \n" +
                "                                        sb.jc as \"sbgs_jc\"\n" +
                "                                    from t_biz_ht  xsht    \n" +
                "                                   left join t_biz_xsd xsd on xsht.id=xsd.xsht_id                    \n" +
                "                                    left join t_sys_gsjg hzf on hzf.id=xsht.hzf_id          \n" +
                "                                  left join t_sys_gsjg sb on sb.id=xsht.sbgs_id  \n" +
                "                                  left join t_biz_ht cght on xsht.zddhcght_id=cght.id              \n" +
                "                             where xsht.type='xsht'and xsht.deleted !=1 and xsd.id is not null";


        if (org.hibernate.tool.hbm2x.StringUtils.isNotEmpty(htbm)) {
            condition.add(" xsht.htbm like :htbm ");
            param.put("htbm", htbm+"%");
        }

        /*if (org.apache.commons.lang3.StringUtils.isNotEmpty(htbm)) {
            condition.add(" ht.htbm like :htbm ");
            param.put("htbm", "%"+htbm + "%");
        }*/
        //String whereSql = "";
        //if (!condition.isEmpty())
        //  whereSql = " and " + org.apache.commons.lang3.StringUtils.join(condition, " and ");
        String countSql = "select count(1) from (" + sql+ ") where 1=1 " ;
        String resultSql = sql ;

        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }

        return page;
    }

    public Map findZjbchtHj(Long xshtId) {
        String sql = "select sum(sbsl) as \"sbsl\",sum(hsje) as \"hsje\" from t_biz_htmx where ht_id = ?";
        return (Map) createSQLQuery(sql, xshtId).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).uniqueResult();
    }
    public Page<Map> findZjbchtmxList1(Long xshtId,int pageNo,int pageSize) {
        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();

        List<String> condition = new ArrayList<String>();
        String sql = "    select   " +
               // "       distinct(htmx.id) as \"htmxId\", " +
                "             xsht.id ,\n" +
                "         gg.code as \"wzgg.code\",\n" +
                "         gg.name as \"wzgg.name\",\n" +
                "         gg.ggxh as \"wzgg.ggxh\",\n" +
                "         gg.cz  as \"wzgg.cz\",\n" +
                "         gg.dw as  \"wzgg.dw\",\n" +
                "         cd.z as \"cd.z\",\n" +
                "          jzfs.z  as \"jzfs.z\",\n" +
                "         htmx.sbsl as \"sbsl\",\n" +
                "         htmx.sbjs as \"sbjs\",\n" +
                "         htmx.hsj  as \"hsj\",\n" +
                "         htmx.bhsj  as  \"bhsj\",\n" +
                "         htmx.hsje as \"hsje\",\n" +
                "         xsmx.sjdj   as \"sjdj\",\n" +
                "         round(xsmx.sjdj*htmx.sbsl,0) as \"sjje\"\n" +
                "   \n" +
                "         from t_biz_htmx  htmx\n" +
                "         left join t_biz_ht xsht on htmx.ht_id=xsht.id\n" +
                "         left join t_biz_xsd xsd on xsd.xsht_id=xsht.id\n" +
                "         left join t_biz_xsmx xsmx on xsmx.xsd_id=xsd.id\n" +
                "         left join t_sys_gg gg  on gg.id=htmx.wz_id         \n" +
                "         left join t_sys_sjzdz cd on cd.id=htmx.sjzdz_cd_id\n" +
                "         left join t_sys_sjzdz jzfs on jzfs.id=htmx.sjzdz_jzfs_id\n" +
                "         where xsht.type='xsht'and xsht.deleted !=1 ";


        if (xshtId != null) {
            condition.add(" xsht.id  = :xshtId ");
            param.put("xshtId", xshtId);
        }
        /*if (org.apache.commons.lang3.StringUtils.isNotEmpty(htbm)) {
            condition.add(" ht.htbm like :htbm ");
            param.put("htbm", "%"+htbm + "%");
        }*/
        //String whereSql = "";
        //if (!condition.isEmpty())
        //  whereSql = " and " + org.apache.commons.lang3.StringUtils.join(condition, " and ");
        String countSql = "select count(1) from (" + sql+ ") where 1=1 " ;
        String resultSql = sql ;

        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }

        return page;
    }

    public List<Map> findZjbchtmxList(Long xshtId) {
        String sql = "    select   " +
                 "       distinct(htmx.id) as \"htmxId\", " +
                "             xsht.id ,\n" +
                "         gg.code as \"wzgg.code\",\n" +
                "         gg.name as \"wzgg.name\",\n" +
                "         gg.ggxh as \"wzgg.ggxh\",\n" +
                "         gg.cz  as \"wzgg.cz\",\n" +
                "         gg.dw as  \"wzgg.dw\",\n" +
                "         cd.z as \"cd.z\",\n" +
                "          jzfs.z  as \"jzfs.z\",\n" +
                "         htmx.sbsl as \"sbsl\",\n" +
                "         htmx.sbjs as \"sbjs\",\n" +
                "         htmx.hsj  as \"hsj\",\n" +
                "         htmx.bhsj  as  \"bhsj\",\n" +
                "         htmx.hsje as \"hsje\",\n" +
                "         xsmx.sjdj   as \"sjdj\",\n" +
                "         round(xsmx.sjdj*htmx.sbsl,0) as \"sjje\"\n" +
                "   \n" +
                "         from t_biz_htmx  htmx\n" +
                "         left join t_biz_ht xsht on htmx.ht_id=xsht.id\n" +
                "         left join t_biz_xsd xsd on xsd.xsht_id=xsht.id\n" +
                "         left join t_biz_xsmx xsmx on xsmx.xsd_id=xsd.id\n" +
                "         left join t_sys_gg gg  on gg.id=htmx.wz_id         \n" +
                "         left join t_sys_sjzdz cd on cd.id=htmx.sjzdz_cd_id\n" +
                "         left join t_sys_sjzdz jzfs on jzfs.id=htmx.sjzdz_jzfs_id\n" +
                "         where xsht.type='xsht'and xsht.deleted !=1  and xsht.id=?";
        return (List<Map>) this.createSQLQuery(sql, xshtId).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
    }


    public Page<Xsht> findZjbcHtList(String zt, String xslx, String htbh, Long ssgsId, Long ssbmId, Long xsrId, Long hzfId, java.util.Date startDate, java.util.Date endDate, String xhtbm, int pageNo, int pageSize) {
        Page<Xsht> page = new Page<Xsht>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");

        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("zddhcght", "cght", CriteriaSpecification.LEFT_JOIN)
                .createAlias("hzf", "hzf", CriteriaSpecification.LEFT_JOIN)
                .createAlias("wlfgs", "wlfgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("wlfbm", "wlfbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("wlfry", "wlfry", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbgs", "sbgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbbm", "sbbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbr", "sbr", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("sbsl").as("sbsl"))
                                .add(Projections.sqlProjection("this_.type as type", new String[]{"type"}, new Type[]{Hibernate.STRING}))
                                .add(Property.forName("id").as("id"))
                                .add(Property.forName("cght.htbm").as("cght_htbm"))
                                .add(Projections.sqlProjection("this_.xsxt_htbm as xsxt_htbm", new String[]{"xsxt_htbm"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("this_.htbm as htbm", new String[]{"htbm"}, new Type[]{Hibernate.STRING}))
                                .add(Property.forName("hzf.qc").as("hzf_qc"))
                                .add(Projections.sqlProjection("this_.zt as zt", new String[]{"zt"}, new Type[]{Hibernate.STRING}))
                                .add(Property.forName("wlfgs.qc").as("wlfgs_qc"))
                                .add(Property.forName("wlfbm.qc").as("wlfbm_qc"))
                                .add(Property.forName("wlfry.xm").as("wlfry_xm"))
                                .add(Property.forName("sbgs.jc").as("sbgs_jc"))
                                .add(Property.forName("sbbm.qc").as("sbbm_qc"))
                                .add(Property.forName("sbr.xm").as("sbr_xm"))
                                .add(Property.forName("zbcg").as("zbcg"))
                                .add(Projections.sqlProjection("this_.qdrq as qdrq", new String[]{"qdrq"}, new Type[]{Hibernate.DATE}))
                                .add(Property.forName("jsrq").as("jsrq"))
                                .add(Property.forName("wlyl").as("wlyl"))
                                .add(Property.forName("processInstanceId").as("processInstanceId"))
                                .add(Projections.sqlProjection("this_.xslx as xslx", new String[]{"xslx"}, new Type[]{Hibernate.STRING}))
                                .add(Property.forName("dateCreated"))
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (StringUtils.isNotEmpty(xslx)) {
            criteria.add(Restrictions.eq("xslx", xslx));
        }
        if (StringUtils.isNotEmpty(zt)) {
            criteria.add(Restrictions.eq("zt", zt));
        }

        if (startDate != null) {
            criteria.add(Restrictions.ge("qdrq", sDaysAfterNowDate(startDate)));
        }

        if (endDate != null) {
            criteria.add(Restrictions.le("qdrq", jDaysAfterNowDate(endDate)));
        } else {
            java.util.Date end = jDaysAfterNowDate(new java.util.Date());
            criteria.add(Restrictions.le("qdrq", end));
        }

        if (StringUtils.isNotEmpty(xhtbm)) {
            criteria.add(Property.forName("xsxt_htbm").like(xhtbm, MatchMode.ANYWHERE));
        }

        if (StringUtils.isNotEmpty(htbh)) {
            criteria.add(Property.forName("htbm").like(htbh, MatchMode.START));
        }

        if (ssgsId != null) {
            criteria.add(Restrictions.eq("sbgs.id", ssgsId));
        }
        if (ssbmId != null) {
            criteria.add(Restrictions.eq("sbbm.id", ssbmId));
        }
        if (xsrId != null) {
            criteria.add(Restrictions.eq("sbr.id", xsrId));
        }
        if (hzfId != null) {
            criteria.add(Restrictions.eq("hzf.id", hzfId));
        }
        RegionDao.createDataRegion(criteria);
        Page list = this.findByCriteria(page, criteria);
        return list;
    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    private java.util.Date sDaysAfterNowDate(int n) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.add(Calendar.DAY_OF_MONTH, -n);
        return rightNow.getTime();
    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    public java.util.Date sDaysAfterNowDate(java.util.Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 0);
        rightNow.set(java.util.Calendar.MINUTE, 0);
        rightNow.set(java.util.Calendar.SECOND, 0);
        return rightNow.getTime();
    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    public java.util.Date jDaysAfterNowDate(java.util.Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 23);
        rightNow.set(java.util.Calendar.MINUTE, 59);
        rightNow.set(java.util.Calendar.SECOND, 59);
        return rightNow.getTime();
    }


    public String findhtce(Long xsdId) {
        String sql = "select\n" +
     /*           "                            \n" +
                "                            \n" +
                "                             xsd.id    as \"xsdId\",                 \n" +*/
                "                             \n" +
                "                             (select NVL(sum(xsmx.sbsl * xsmx.hsj), 0)\n" +
                "                                from t_biz_xsd x\n" +
                "                                left join t_biz_xsmx xsmx\n" +
                "                                  on xsmx.xsd_id = x.id\n" +
                "                               where x.xsht_id = xsht.id\n" +
                "                                 and x.deleted != 1) -\n" +
                "                             (select NVL(sum(xsmx.sbsl * xsmx.sjdj), 0)\n" +
                "                                from t_biz_xsd x\n" +
                "                                left join t_biz_xsmx xsmx\n" +
                "                                  on xsmx.xsd_id = x.id\n" +
                "                               where x.xsht_id = xsht.id\n" +
                "                                 and x.deleted != 1) as \"ce\"\n" +
                "                            \n" +
                "                              from t_biz_ht xsht\n" +
                "                            \n" +
                "                              left join t_biz_xsd xsd\n" +
                "                                on xsht.id = xsd.xsht_id\n" +
                "                              left join t_sys_gsjg hzf\n" +
                "                                on hzf.id = xsht.hzf_id\n" +
                "                              left join t_sys_gsjg sb\n" +
                "                                on sb.id = xsht.sbgs_id\n" +
                "                              left join t_biz_ht cght\n" +
                "                                on xsht.zddhcght_id = cght.id\n" +
                "                            \n" +
                "                             where xsht.type = 'xsht' \n" +
                "                               and xsht.deleted != 1 and xsd.id=?";
        return (String) createSQLQuery(sql).setParameter(0, xsdId).uniqueResult();
    }

}
